#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd

from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob


class DB22MPPJob(AbstractDPJob):
    """
    通过两个SQL去取来源表和目标表的表结构

    传入6个参数   来源标  和目标表  及开始时间结束时间

    将来源表那段时间的数据取出来  在存到目标表里
    """
    source_schema = None
    source_table = None
    target_schema = None
    target_table = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_source_schema=None,
                 p_source_table=None,
                 p_target_schema=None,
                 p_target_table=None,
                 p_account_period_start=None,
                 p_account_period_end=None):

        super(DB22MPPJob, self).__init__(p_config=p_config,
                                         p_db_conn_mpp=p_db_conn_mpp,
                                         p_db_conn_rds=p_db_conn_rds,
                                         p_db_conn_dbprod7=p_db_conn_dbprod7,
                                         p_unit=None,
                                         p_account=None,
                                         p_cost_center=None,
                                         p_account_period_start=p_account_period_start,
                                         p_account_period_end=p_account_period_end)

        self.source_schema = p_source_schema
        self.source_table = p_source_table
        self.target_schema = p_target_schema
        self.target_table = p_target_table
        pass

    def do_execute(self):
        """
        """
        self.logger.info('DB22MPPJob.do_execute')
        sql = " SELECT COLNAME FROM " \
              " SYSCAT.columns" \
              " WHERE 1=1 " \
              " AND TABSCHEMA  = '%s' AND TABNAME= '%s'" % (
                  self.source_schema, self.source_table)
        self.logger.info(sql)

        df1 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df1.empty is False
        if success is False:
            return
        df1.columns = df1.columns.str.upper()
        self.logger.info('df1_properties-')
        # df1_properties = df1.columns.values
        df1_properties = df1['COLNAME'].values.tolist()
        self.logger.info(",".join(df1_properties))
        if 'WORK_TIME' in df1_properties:
            time_field = 'WORK_TIME'
            time_field_mpp = 'PRODUCE_TIME'
            account_period_start = self.account_period_start
            account_period_end = self.account_period_end
        elif 'WORK_DATE' in df1_properties:
            time_field = 'WORK_DATE'
            time_field_mpp = 'PRODUCE_TIME'
            account_period_start = self.account_period_start
            account_period_end = self.account_period_end
        elif 'DATE' in df1_properties:
            time_field = 'DATE'
            time_field_mpp = 'DATE'
            account_period_start = self.account_period_start[0:8]
            account_period_end = self.account_period_end[0:8]
        else:
            # FIXME 写程序尽量要从理论上保证其完备性。
            # FIXME 此处理应做出处理。例如可以直接return.
            # FIXME 也可以把 time_field, time_field_mpp, account_period_start, account_period_end赋合适的值，以便程序能继续走下去
            pass
        if self.target_table == 'T_ADS_FACT_SICB_MO0101':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " SHIFT AS SHIFT," \
                  " WORK_TIME AS PRODUCE_TIME," \
                  " MAT_NO AS PROD_COILNO," \
                  " IN_MAT_NO AS ENTRY_COILNO," \
                  " EDITION AS IND," \
                  " EDITION_NAME AS IND_NAME," \
                  " EDITION_UNIT AS IND_UNIT," \
                  " EDITION_VALUE_FACT as ACT_VALUE," \
                  " EDITION_VALUE_PLAN AS PLAN_VALUE," \
                  " EDITION_VALUE_GOAL AS TARGET_VALUE," \
                  " EDITION_VALUE_DIFF AS DIFF_VALUE," \
                  " LIMIT_UP AS TARGET_RANGE_UL," \
                  " LIMIT_DOWN AS TARGET_RANGE_LL," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CA0001':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " SHIFT AS SHIFT," \
                  " WORK_TIME AS PRODUCE_TIME," \
                  " PROCESS_START_TIME AS PRODUCE_START_TIME," \
                  " PROCESS_END_TIME AS PRODUCE_END_TIME," \
                  " PRODUCT_CODE AS BYPRODUCT_CODE," \
                  " ST_NO AS STEELNO," \
                  " MAT_ACT_WIDTH AS MAT_ACT_WIDTH," \
                  " WIDTH as WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " MAT_ACT_THICK AS MAT_ACT_THICK," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " MAT_NO AS PROD_COILNO," \
                  " ITEM_ID AS ITEM_ID," \
                  " JHZ_KEY AS PLAN_SYS_KV_CODE," \
                  " JHZ_KEY_NAME AS PLAN_SYS_KV_CODE_NAME," \
                  " IN_PRODUCT_CODE AS INPUT_BYPRODUCT_CODE," \
                  " IN_MAT_NO AS ENTRY_COILNO," \
                  " WT AS OUTPUT_WT," \
                  " ACT_WT AS ACT_OUTPUT_WT," \
                  " IN_WT AS INPUT_WT," \
                  " ACT_IN_WT AS ACT_INPUT_WT," \
                  " WCE AS COST_SUBJECT," \
                  " WCE_NAME AS COST_SUBJECT_CNAME," \
                  " WCE_UNIT AS COST_SUBJECT_UNIT," \
                  " ACT_N as COST_SUBJECT_ON_AMT," \
                  " CONSUME as UNITCONSUME," \
                  " ACT_AMT AS PROC_ON_AMT," \
                  " UNIT_AMT AS UNIT_PROC_ON_AMT," \
                  " FAC_AMT AS DEPT_PROC_ON_AMT," \
                  " PRICE_T AS TON_STEEL_PRICE," \
                  " PRICE AS COST_SUBJECT_PRICE," \
                  " B_CONSUME AS STD_UNITCONSUME," \
                  " B_ACT_N AS COST_SUBJECT_STD_AMT," \
                  " B_ACT_AMT AS PROC_STD_AMT," \
                  " B_UNIT_AMT AS UNIT_PROC_STD_AMT," \
                  " B_FAC_AMT AS DEPT_PROC_STD_AMT," \
                  " PRICE_TB AS STD_TON_STEEL_PRICE," \
                  " APP_THROW_AI_MODE AS APPTHROWAIMODE," \
                  " LAYER_TYPE AS COATING_TYPE," \
                  " MAT_ACT_LEN AS MAT_ACT_LEN, " \
                  " MAT_ACT_AREA AS MAT_ACT_AREA," \
                  " TOP_COAT_WT AS TOP_COATING_WT," \
                  " BOT_COAT_WT AS BOT_COATING_WT," \
                  " DATA_TYPE AS DATA_TYPE," \
                  " TYPE AS CALC_END," \
                  " REC_REVISOR AS REC_REVISOR," \
                  " REC_REVISOR_TIME AS REC_REVISOR_TIME," \
                  " DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE," \
                  " TRIM_FLAG AS TRIM_FLAG," \
                  " IN_MAT_WIDTH AS ENTRY_MAT_WIDTH," \
                  " IN_WIDTH AS ENTRY_WIDTH," \
                  " IN_WIDTH_COD AS ENTRY_WIDTH_CODE," \
                  " IN_MAT_THICK AS ENTRY_MAT_THICK," \
                  " IN_THICK AS ENTRY_THICK," \
                  " IN_THICK_COD AS ENTRY_THICK_CODE," \
                  " PLAN_NO AS PLAN_NO," \
                  " PLAN_NO_C AS PLAN_NO_C," \
                  " PLAN_NO_COD AS PLAN_NO_CODE," \
                  " TRIM_WIDTH AS TRIMM_WIDTH," \
                  " TRIM_WIDTH_C AS TRIMMING_WIDTH_RANGE," \
                  " TRIM_WIDTH_COD AS TRIMMING_WIDTH_CODE," \
                  " IN_MAT_INNER_DIA AS ENTRY_MAT_INDIA," \
                  " IN_MAT_DIV_CODE AS IN_MAT_DIV_CODE," \
                  " IN_MAT_DIV AS IN_MAT_DIV," \
                  " LAS_NOTCH_FLAG AS PRODUCE_NICK_FLAG," \
                  " QS_CODE AS PROD_CAT_CODE," \
                  " QS_NAME AS PROD_CAT," \
                  " REC_REVISOR AS REC_CREATOR," \
                  " REC_REVISOR_TIME AS REC_CREATE_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND COST_CENTER in ('MIDN','MIDM','MIDY','MIDZ','MICX','MIHY','MIHZ','MICZ')" \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0101':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " IN_MAT_NO AS ENTRY_COILNO," \
                  " MAT_NO AS PROD_COILNO," \
                  " WORK_TIME AS PRODUCE_TIME," \
                  " WIDTH AS WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " ST_NO AS STEELNO," \
                  " TOTALCOST_AMT_FACT as ACT_TOTAL_COST," \
                  " TOTALCOST_AMT_GOAL AS TARGET_TOTAL_COST," \
                  " VARIABLECOST_AMT_FACT AS ACT_CHANGE_COST," \
                  " VARIABLECOST_AMT_GOAL AS TARGET_CHANGE_COST," \
                  " FIXEDCOST_AMT_FACT AS ACT_REVISE_COST," \
                  " FIXEDCOST_AMT_GOAL AS TARGET_REVISE_COST," \
                  " COST1_AMT_FACT AS MAIN_MATERIAL_COST," \
                  " COST2_AMT_FACT AS ENERGYCONSUM_COST," \
                  " COST3_AMT_FACT AS ACCES_COST," \
                  " COST4_AMT_FACT AS ROLL_COST," \
                  " COST5_AMT_FACT AS OTHER_COST," \
                  " COST6_AMT_FACT AS COST_6," \
                  " COST7_AMT_FACT AS COST_7," \
                  " COST8_AMT_FACT AS COST_8," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0102':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " DATE AS DATE," \
                  " WIDTH AS WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " TOTALCOST_AMT_FACT as ACT_TOTAL_COST," \
                  " TOTALCOST_AMT_GOAL AS TARGET_TOTAL_COST," \
                  " VARIABLECOST_AMT_FACT AS ACT_CHANGE_COST," \
                  " VARIABLECOST_AMT_GOAL AS TARGET_CHANGE_COST," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0103':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " DATE AS DATE," \
                  " WIDTH AS WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " ST_NO AS STEELNO," \
                  " TOTALCOST_AMT_FACT as ACT_TOTAL_COST," \
                  " TOTALCOST_AMT_GOAL AS TARGET_TOTAL_COST," \
                  " VARIABLECOST_AMT_FACT AS ACT_CHANGE_COST," \
                  " VARIABLECOST_AMT_GOAL AS TARGET_CHANGE_COST," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0104':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " DATE AS DATE," \
                  " WIDTH AS WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " ST_NO AS STEELNO," \
                  " TOTALCOST_AMT_FACT as ACT_TOTAL_COST," \
                  " TOTALCOST_AMT_GOAL AS TARGET_TOTAL_COST," \
                  " VARIABLECOST_AMT_FACT AS ACT_CHANGE_COST," \
                  " VARIABLECOST_AMT_GOAL AS TARGET_CHANGE_COST," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0105':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " DATE AS DATE," \
                  " TOTALCOST_AMT_FACT as ACT_TOTAL_COST," \
                  " TOTALCOST_AMT_GOAL AS TARGET_TOTAL_COST," \
                  " VARIABLECOST_AMT_FACT AS ACT_CHANGE_COST," \
                  " VARIABLECOST_AMT_GOAL AS TARGET_CHANGE_COST," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0106':
            sql = " SELECT " \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " DATE AS DATE," \
                  " TOTALCOST_AMT_FACT as ACT_TOTAL_COST," \
                  " TOTALCOST_AMT_GOAL AS TARGET_TOTAL_COST," \
                  " VARIABLECOST_AMT_FACT AS ACT_CHANGE_COST," \
                  " VARIABLECOST_AMT_GOAL AS TARGET_CHANGE_COST," \
                  " COST1_AMT_FACT AS MAIN_MATERIAL_COST," \
                  " COST2_AMT_FACT AS ENERGYCONSUM_COST," \
                  " COST3_AMT_FACT AS ACCES_COST," \
                  " COST4_AMT_FACT AS ROLL_COST," \
                  " COST5_AMT_FACT AS OTHER_COST," \
                  " COST6_AMT_FACT AS COST_6," \
                  " COST7_AMT_FACT AS COST_7," \
                  " COST8_AMT_FACT AS COST_8," \
                  " REC_REVISOR AS REC_CREATOR," \
                  " REC_REVISOR_TIME AS REC_CREATE_TIME," \
                  " REC_REVISOR AS REC_REVISOR," \
                  " REC_REVISOR_TIME AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        if self.target_table == 'T_ADS_FACT_SICB_CD0201':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT_CODE AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " SHIFT AS SHIFT," \
                  " WORK_DATE AS PRODUCE_TIME," \
                  " MAT_NO AS PROD_COILNO," \
                  " IN_MAT_NO AS ENTRY_COILNO," \
                  " ST_NO AS STEELNO," \
                  " TOTALCOST_AMT_FACT AS ACT_TOTAL_COST," \
                  " COST1_FACT AS MAIN_MATERIAL_COST," \
                  " COST2_FACT AS ENERGYCONSUM_COST," \
                  " COST3_FACT AS ACCES_COST," \
                  " COST4_FACT AS ROLL_COST," \
                  " COST5_FACT AS OTHER_COST," \
                  " COST6_FACT as COST_6," \
                  " COST7_FACT AS COST_7," \
                  " COST8_FACT AS COST_8," \
                  " ACT_WT AS ACT_OUTPUT_WT," \
                  " ACT_IN_WT AS ACT_INPUT_WT," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " WIDTH AS WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE," \
                  " TRIM_FLAG AS TRIM_FLAG," \
                  " IN_WIDTH AS ENTRY_WIDTH," \
                  " IN_WIDTH_COD AS ENTRY_WIDTH_CODE," \
                  " IN_THICK AS ENTRY_THICK," \
                  " IN_THICK_COD AS ENTRY_THICK_CODE," \
                  " PLAN_NO_C AS PLAN_NO_C," \
                  " PLAN_NO_COD AS PLAN_NO_CODE," \
                  " TRIM_WIDTH_C AS TRIMMING_WIDTH_RANGE," \
                  " TRIM_WIDTH_COD AS TRIMMING_WIDTH_CODE," \
                  " IN_MAT_DIV_CODE AS IN_MAT_DIV_CODE," \
                  " IN_MAT_DIV AS IN_MAT_DIV," \
                  " LAS_NOTCH_FLAG AS PRODUCE_NICK_FLAG," \
                  " QS_CODE AS PROD_CAT_CODE," \
                  " QS_NAME AS PROD_CAT," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
            df0['ACCT'].fillna(value='', inplace=True)
            df0['COST_CENTER'].fillna(value='', inplace=True)
            df0['UNIT_CODE'].fillna(value='', inplace=True)
            df0['CLASS'].fillna(value='', inplace=True)
            df0['SHIFT'].fillna(value='', inplace=True)
            df0['PRODUCE_TIME'].fillna(value='', inplace=True)
            df0['PROD_COILNO'].fillna(value='', inplace=True)
            df0['REC_CREATOR'].fillna(value='', inplace=True)
            df0['REC_CREATE_TIME'].fillna(value='', inplace=True)
            df0['REC_REVISOR'].fillna(value='', inplace=True)
            df0['REC_REVISOR_TIME'].fillna(value='', inplace=True)

        if self.target_table == 'T_ADS_FACT_SICB_DP0102':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " WORK_TIME AS PRODUCE_TIME," \
                  " PROCESS_START_TIME AS PRODUCE_START_TIME," \
                  " PROCESS_END_TIME AS PRODUCE_END_TIME," \
                  " PRODUCT_CODE AS BYPRODUCT_CODE," \
                  " ST_NO AS STEELNO," \
                  " MAT_NO AS PROD_COILNO," \
                  " IN_PRODUCT_CODE AS INPUT_BYPRODUCT_CODE," \
                  " IN_MAT_NO AS ENTRY_COILNO," \
                  " WT as OUTPUT_WT," \
                  " ACT_WT AS ACT_OUTPUT_WT," \
                  " IN_WT AS INPUT_WT," \
                  " ACT_IN_WT AS ACT_INPUT_WT," \
                  " APP_THROW_AI_MODE AS APPTHROWAIMODE," \
                  " DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE," \
                  " TRIM_FLAG AS TRIM_FLAG," \
                  " MAT_ACT_WIDTH AS MAT_ACT_WIDTH," \
                  " MAT_ACT_THICK AS MAT_ACT_THICK," \
                  " IN_MAT_WIDTH AS ENTRY_MAT_WIDTH," \
                  " IN_MAT_THICK AS ENTRY_MAT_THICK," \
                  " PLAN_NO AS PLAN_NO," \
                  " TRIM_WIDTH AS TRIMMING_AMT," \
                  " IN_MAT_INNER_DIA AS ENTRY_MAT_INDIA," \
                  " CUST_ORDER_NO AS CUST_ORDER_NO," \
                  " PICKL_TRIM_FLAG AS PICKLING_TRIMMING_FLAG," \
                  " SORT_GRADE_CODE AS SORT_GRADE_CODE," \
                  " LAYER_TYPE as COATING_TYPE," \
                  " MAT_ACT_LEN AS MAT_ACT_LEN," \
                  " MAT_ACT_AREA AS MAT_ACT_AREA," \
                  " TOP_COAT_WT AS TOP_COATING_WT," \
                  " BOT_COAT_WT AS BOT_COATING_WT," \
                  " LAS_NOTCH_FLAG AS PRODUCE_NICK_FLAG," \
                  " SHIFT AS SHIFT," \
                  " DATA_TYPE AS DATA_TYPE," \
                  " REC_REVISOR AS REC_REVISOR," \
                  " REC_REVISOR_TIME AS REC_REVISOR_TIME," \
                  " REC_REVISOR AS REC_CREATOR," \
                  " REC_REVISOR_TIME AS REC_CREATE_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
            df0['ACCT'].fillna(value='', inplace=True)
            df0['DEPARTMENT_CODE'].fillna(value='', inplace=True)
            df0['COST_CENTER'].fillna(value='', inplace=True)
            df0['UNIT_CODE'].fillna(value='', inplace=True)
            df0['CLASS'].fillna(value='', inplace=True)
            df0['PRODUCE_TIME'].fillna(value='', inplace=True)
            df0['BYPRODUCT_CODE'].fillna(value='', inplace=True)
            df0['PROD_COILNO'].fillna(value='', inplace=True)
            df0['REC_REVISOR'].fillna(value='', inplace=True)
            df0['REC_REVISOR_TIME'].fillna(value='', inplace=True)
            df0['REC_CREATOR'].fillna(value='', inplace=True)
            df0['REC_CREATE_TIME'].fillna(value='', inplace=True)

        if self.target_table == 'T_ADS_FACT_SICB_DP0103':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " FACTORY AS DEPARTMENT_CODE," \
                  " COST_CENTER AS COST_CENTER," \
                  " UNIT AS UNIT_CODE," \
                  " TEAM AS CLASS," \
                  " SHIFT AS SHIFT," \
                  " WORK_TIME AS PRODUCE_TIME," \
                  " PROCESS_START_TIME AS PRODUCE_START_TIME," \
                  " PROCESS_END_TIME AS PRODUCE_END_TIME," \
                  " PRODUCT_CODE AS BYPRODUCT_CODE," \
                  " ST_NO AS STEELNO," \
                  " MAT_NO AS PROD_COILNO," \
                  " ITEM_ID as ITEM_ID," \
                  " JHZ_KEY AS PLAN_SYS_KV_CODE," \
                  " JHZ_KEY_NAME AS PLAN_SYS_KV_CODE_NAME," \
                  " IN_PRODUCT_CODE AS INPUT_BYPRODUCT_CODE," \
                  " IN_MAT_NO AS ENTRY_COILNO," \
                  " WT AS OUTPUT_WT," \
                  " ACT_WT AS ACT_OUTPUT_WT," \
                  " IN_WT AS INPUT_WT," \
                  " ACT_IN_WT AS ACT_INPUT_WT," \
                  " WCE AS COST_SUBJECT," \
                  " ACT_N AS COST_SUBJECT_ON_AMT," \
                  " CONSUME AS UNITCONSUME," \
                  " CONSUME_ITEM AS CONSUME_PROJ," \
                  " CONSUME_DESC AS CONSUME_PROJ_DESC," \
                  " CONSUME_UNIT AS CONSUME_PROJ_UNIT," \
                  " CONSUME_N AS CONSUME_AMT," \
                  " APP_THROW_AI_MODE AS APPTHROWAIMODE," \
                  " DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE," \
                  " TRIM_FLAG as TRIM_FLAG," \
                  " MAT_ACT_WIDTH as MAT_ACT_WIDTH," \
                  " WIDTH AS WIDTH," \
                  " WIDTH_COD AS WIDTH_CODE," \
                  " MAT_ACT_THICK AS MAT_ACT_THICK," \
                  " THICK AS THICK," \
                  " THICK_COD AS THICK_CODE," \
                  " IN_MAT_WIDTH AS ENTRY_MAT_WIDTH," \
                  " IN_WIDTH AS ENTRY_WIDTH," \
                  " IN_WIDTH_COD AS ENTRY_WIDTH_CODE," \
                  " IN_MAT_THICK AS ENTRY_MAT_THICK," \
                  " IN_THICK AS ENTRY_THICK," \
                  " IN_THICK_COD AS ENTRY_THICK_CODE," \
                  " PLAN_NO AS PLAN_NO," \
                  " PLAN_NO_C AS PLAN_NO_C," \
                  " PLAN_NO_COD AS PLAN_NO_CODE, " \
                  " TRIM_WIDTH AS TRIMM_WIDTH," \
                  " TRIM_WIDTH_C AS TRIMMING_WIDTH_RANGE," \
                  " TRIM_WIDTH_COD AS TRIMMING_WIDTH_CODE," \
                  " IN_MAT_INNER_DIA AS ENTRY_MAT_INDIA," \
                  " IN_MAT_DIV_CODE AS IN_MAT_DIV_CODE," \
                  " IN_MAT_DIV AS IN_MAT_DIV," \
                  " CUST_ORDER_NO AS CUST_ORDER_NO," \
                  " PICKL_TRIM_FLAG AS PICKLING_TRIMMING_FLAG," \
                  " SORT_GRADE_CODE AS SORT_GRADE_CODE," \
                  " LAYER_TYPE AS COATING_TYPE," \
                  " MAT_ACT_LEN AS MAT_ACT_LEN," \
                  " MAT_ACT_AREA AS MAT_ACT_AREA," \
                  " TOP_COAT_WT AS TOP_COATING_WT," \
                  " BOT_COAT_WT AS BOT_COATING_WT," \
                  " LAS_NOTCH_FLAG AS PRODUCE_NICK_FLAG," \
                  " QS_CODE AS PROD_CAT_CODE," \
                  " QS_NAME AS PROD_CAT," \
                  " DATA_TYPE AS DATA_TYPE," \
                  " REC_REVISOR AS REC_REVISOR," \
                  " REC_REVISOR_TIME AS REC_REVISOR_TIME," \
                  " REC_REVISOR AS REC_CREATOR," \
                  " REC_REVISOR_TIME AS REC_CREATE_TIME" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
            df0['ACCT'].fillna(value='', inplace=True)
            df0['DEPARTMENT_CODE'].fillna(value='', inplace=True)
            df0['COST_CENTER'].fillna(value='', inplace=True)
            df0['UNIT_CODE'].fillna(value='', inplace=True)
            df0['CLASS'].fillna(value='', inplace=True)
            df0['SHIFT'].fillna(value='', inplace=True)
            df0['PRODUCE_TIME'].fillna(value='', inplace=True)
            df0['BYPRODUCT_CODE'].fillna(value='', inplace=True)
            df0['PROD_COILNO'].fillna(value='', inplace=True)
            df0['COST_SUBJECT'].fillna(value='', inplace=True)
            df0['DATA_TYPE'].fillna(value='', inplace=True)
            df0['REC_CREATOR'].fillna(value='', inplace=True)
            df0['REC_CREATE_TIME'].fillna(value='', inplace=True)



        if self.target_table == 'T_ADS_FACT_SICB_BZSJCB_0':
            sql = " SELECT " \
                  " ACCOUNT as ACCT," \
                  " MAT_NO AS PROD_COILNO," \
                  " MAT_ACT_WT AS MAT_ACT_WT," \
                  " WORK_TIME AS PRODUCE_TIME," \
                  " PACK_TYPE_CODE AS PACK_TYPE_CODE," \
                  " QS_NAME AS PROD_CAT," \
                  " BZZC AS OTHER_FEE," \
                  " BZCB AS COST_PACK" \
                  " FROM " \
                  " %s.%s" \
                  " WHERE 1=1 " \
                  " AND %s >= '%s'" \
                  " AND %s < '%s'" % (
                      self.source_schema, self.source_table, time_field, account_period_start, time_field,
                      account_period_end)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()
        sql = " DELETE FROM " \
              " %s.%s" \
              " WHERE 1=1 " \
              " AND %s >= '%s'" \
              " AND %s < '%s'" % (
                  self.target_schema, self.target_table, time_field_mpp, account_period_start, time_field_mpp,
                  account_period_end)
#              " AND UNIT_CODE in ('Q602','Q512','Q503','Q414','Q314','Q412','Q402','Q502','Q403')" \
        self.logger.info(sql)
        self.db_conn_rds.execute(sql)

        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name=self.target_table, p_schema=self.target_schema,
                       p_dataframe=df0,
                       p_max_times=5).redo()



        super(DB22MPPJob, self).do_execute()
